package com.csist.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @ClassName: JDBCUtil
 * @Description: JDBC����MySQL������
 * @author: CT
 * @date: 2021��8��4������9:00:58
 * @version: 1.0
 */
public class JDBCUtil {

	private static String driver;
	private static String url;
	private static String user;
	private static String password;
	private static Connection conn;
	private static PreparedStatement ps;
	private static ResultSet rs;

	private JDBCUtil() {

	}

	/**
	 * ��ȡ���ݿ�����������Ϣ
	 */
	static {
		Properties prop = new Properties();
		InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("database.properties");
		try {
			prop.load(is);

			driver = prop.getProperty("driver");
			url = prop.getProperty("url");
			user = prop.getProperty("user");
			password = prop.getProperty("password");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * ע������
	 */
	static {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * ��ȡ���ݿ����Ӷ���
	 * 
	 * @return ���ݿ����Ӷ���
	 */
	public static Connection getConnection() {
		try {
			return DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * ִ��DML���
	 * 
	 * @param sql
	 *            ��ִ�е�SQL���
	 * @param param
	 *            �������ɱ䳤�ȵĲ�����
	 * @return ��Ӱ�������(Ϊ0��ʾ����ʧ��)
	 */
	public static int executeUpdate(String sql, Object... param) {
		conn = getConnection();
		int result = 0;// ���ڴ洢ִ�н����Ϊ0��ʾDML����ʧ��
		try {
			ps = conn.prepareStatement(sql);
			// ΪPreparedStatement���������ֵ
			if (param != null) {
				for (int i = 0; i < param.length; i++) {
					ps.setObject(i + 1, param[i]);// ע�⣺��һ����������ʼֵ��1��ʼ
				}
			}
			result = ps.executeUpdate();// ִ��SQL��䲢��ȡ����
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(conn, ps);
		}
		return result;// ���ؽ��
	}

	/**
	 * ִ��DML��䣨֧������δ�ͷ���Դ��
	 * 
	 * @param conn
	 *            ���Ӷ���
	 * @param sql
	 *            ��ִ�е�SQL���
	 * @param param
	 *            �������ɱ䳤�ȵĲ�����
	 * @return ��Ӱ�������(Ϊ0��ʾ����ʧ��)
	 */
	public static int executeUpdate(Connection conn, String sql, Object... param) {
		int result = 0;
		try {
			ps = conn.prepareStatement(sql);
			if (param != null) {
				for (int i = 0; i < param.length; i++) {
					ps.setObject(i + 1, param[i]);// ע�⣺��һ����������ʼֵ��1��ʼ
				}
			}
			result = ps.executeUpdate();// ִ��SQL��䲢��ȡ����
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;// ���ؽ��
	}

	/**
	 * ִ��DQL��䣨δ�ͷ���Դ��
	 * 
	 * @param sql
	 *            ��ִ�е�SQL���
	 * @param param
	 *            �������ɱ䳤�ȵĲ�����
	 * @return ���ز�ѯ�����(ע��δ�ر����ݿ�����)
	 */
	public static ResultSet executeQuery(String sql, Object... param) {
		conn = getConnection();
		try {
			ps = conn.prepareStatement(sql);
			if (param != null) {
				for (int i = 0; i < param.length; i++) {
					ps.setObject(i + 1, param[i]);
				}
			}
			rs = ps.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * ִ��DQL��䣨֧������δ�ͷ���Դ��
	 * 
	 * @param conn
	 *            ���Ӷ���
	 * @param sql
	 *            ��ִ�е�SQL���
	 * @param param
	 *            �������ɱ䳤�ȵĲ�����
	 * @return ���ز�ѯ�����
	 */
	public static ResultSet executeQuery(Connection conn, String sql, Object... param) {
		try {
			ps = conn.prepareStatement(sql);
			if (param != null) {
				for (int i = 0; i < param.length; i++) {
					ps.setObject(i + 1, param[i]);
				}
			}
			rs = ps.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * �ͷ���Դ
	 * 
	 * @param conn
	 *            ���ݿ����Ӷ���
	 * @param ps
	 *            SQL���ִ�ж���
	 */
	public static void close(Connection conn, PreparedStatement ps) {
		close(conn, ps, rs);
	}

	/**
	 * �ͷ���Դ
	 * 
	 * @param conn
	 *            ���ݿ����Ӷ���
	 * @param rs
	 *            ��ѯ���������
	 */
	public static void close(Connection conn, ResultSet rs) {
		close(conn, ps, rs);
	}

	/**
	 * �ͷ���Դ
	 * 
	 * @param rs
	 *            ��ѯ���������
	 */
	public static void close(ResultSet rs) {
		close(conn, ps, rs);
	}

	/**
	 * �ͷ���Դ
	 * 
	 * @param conn
	 *            ���ݿ����Ӷ���
	 * @param ps
	 *            SQL���ִ�ж���
	 * @param rs
	 *            ��ѯ���������
	 */
	public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {

		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}
